package com.wdzl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.Vector;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.data.general.DefaultPieDataset;

import com.wdzl.db.DBManager;
import com.wdzl.entity.Student;
import com.wdzl.util.JFreechartUtil;

public class StudentDao extends DBManager{
	/**
	 * 添加学员
	 */
	public boolean addStudent(Student student){
		String sql = "insert into stuTbl(sname,sphone,semail,sdatetime,age,schoolname,ssex)values(?,?,?,?,?,?,?)";
		Object[] param = {student.getSname(),student.getPhone(),student.getEmail(),student.getAddDate(),student.getSage(),student.getSchoolname(),student.isSex()};
		int rows = super.update(sql, param);
		return rows>0;
	}
	/**
	 * 根据学员编号查询
	 * @param sid
	 * @return
	 */
	public Student queryStudentById(Integer sid){
		String sql = "select sname,sphone,semail,sdatetime,age,schoolname,ssex from stutbl where sid=?";
		Integer[] param = {sid};
		ResultSet rst = super.query(sql, param);
		try {
			if(rst.next()){
				Student student = new Student();
				student.setAddDate(rst.getDate("sdatetime"));
				student.setEmail(rst.getString("semail"));
				student.setPhone(rst.getString("sphone"));
				student.setSage(rst.getInt("age"));
				student.setSchoolname(rst.getString("schoolname"));
				student.setSex(rst.getBoolean("ssex"));
				student.setSid(sid);
				student.setSname(rst.getString("sname"));

				return student;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			super.close();
		}
		return null;
	}
	/**
	 * 修改学员信息
	 */
	public boolean update(Student student){
		String sql = "update stutbl set sname=?,age=?,sphone=?,semail=?,schoolname=?,ssex=? where sid=?";
		Object[] param = {student.getSname(),student.getSage(),student.getPhone(),student.getEmail(),student.getSchoolname(),student.isSex(),student.getSid()};
		return super.update(sql, param)>0;
	}
	/**
	 * 删除学员信息
	 */
	public boolean delStudent(Integer sid){
		String sql = "delete from stutbl where sid=?";
		Object[] param = {sid};
		return super.update(sql, param)>0;
	}
	/**
	 * 根据搜索条件 查询列表
	 */
	public Vector<Vector<Object>> search(Student student){
		String sql = "select * from stutbl where 1=1";

		List<Object> list = new ArrayList<Object>();

		if(student.getSname()!=null && !student.getSname().equals("")){
			sql += " and sname like ?";
			list.add("%"+student.getSname()+"%");
		}
		if(student.getSchoolname()!=null && !student.getSchoolname().equals("")){
			sql += " and schoolname=?";
			list.add(student.getSchoolname());
		}
		if(student.getStartDate()!=null){
			sql += " and sdatetime>=?";
			list.add(student.getStartDate());
		}
		if(student.getEndDate()!=null ){
			sql += " and sdatetime<=?";
			list.add(student.getEndDate());
		}
		Object[] param = list.toArray();//转为数组

		ResultSet rst = super.query(sql, param);
		Vector<Vector<Object>> data = new Vector<Vector<Object>>();

		try {
			while(rst.next()){
				Vector<Object> row = new Vector<Object>();
				row.add(rst.getString("sid"));
				row.add(rst.getString("sname"));
				if(rst.getBoolean("ssex")){
					row.add("男");
				}else{
					row.add("女");
				}
				row.add(rst.getString("age"));
				row.add(rst.getString("sphone"));
				row.add(rst.getString("schoolname"));
				row.add(rst.getString("semail"));
				row.add(rst.getString("sdatetime"));

				data.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			super.close();
		}

		return data;
	}
	/**
	 * 根据数据获取饼图
	 */
	public ChartPanel getPiePanel(){
		HashMap<String, Integer> mapData = statisStudent();
		//1.数据采集
		DefaultPieDataset data = new DefaultPieDataset();
		Set<String> keys = mapData.keySet();
		for (String string : keys) {
			data.setValue(string, mapData.get(string));
		}
		//2.使用数据集 创建chart对象
		JFreeChart chart = ChartFactory.createPieChart("学校人数分布图", data, true, true, Locale.CHINA);
		//处理乱码
		JFreechartUtil.setChartFont(chart);

		//3.使用chart对象 生成面板
		ChartPanel cp = new ChartPanel(chart);

		return cp;
	}
	/**
	 * 根据学校统计学员人数
	 */
	public HashMap<String, Integer> statisStudent(){
		HashMap<String, Integer> res = new HashMap<String, Integer>();
		String sql = "SELECT sc.scname,COUNT(st.schoolname) num FROM school sc LEFT JOIN stutbl st ON sc.`scname`=st.`schoolName` GROUP BY sc.`scName`";
		ResultSet rst = super.query(sql, null);
		try {
			while(rst.next()){
				res.put(rst.getString("scname"), rst.getInt("num"));//学校名和学校人数
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			super.close();
		}
		return res;
	}
}
